﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using CinemaBookingProject.DBLayer;
using System.Data;

public class DBMovie : DataBase
{

    public Movie Read(string title)
    {
        SqlDataReader rdr = null;
        Movie movie = null;

        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * from Movie where Title Like @title", Connection);

            cmd.Parameters.Add("@title", SqlDbType.VarChar).Value = title;

            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                movie = new Movie((string)rdr["Title"], (string)rdr["MovieDescription"], (double)rdr["Price"], (bool)rdr["Is3D"], (int)rdr["MovieLength"], (int)rdr["Commercial"], (int)rdr["Trailer"], (string)rdr["MovieImage"]);

            }

        }
        finally
        {
            //close reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close connection
            if (Connection != null)
            {
                Connection.Close();
            }
        }
        return movie;
    }

    public Movie Read(int id)
    {
        SqlDataReader rdr = null;
        Movie movie = null;

        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * from Movie where ID = @id", Connection);

            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                movie = new Movie((string)rdr["Title"], (string)rdr["MovieDescription"], (double)rdr["Price"], (bool)rdr["Is3D"], (int)rdr["MovieLength"], (int)rdr["Commercial"], (int)rdr["Trailer"], (string)rdr["MovieImage"]);

            }

        }
        finally
        {
            //close reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close connection
            if (Connection != null)
            {
                Connection.Close();
            }
        }
        return movie;
    }

    public List<Movie> ReadAll()
    {
        SqlDataReader rdr = null;
        List<Movie> movies = new List<Movie>();

        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * from Movie" , Connection);

            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                movies.Add(new Movie((string)rdr["Title"], (string)rdr["MovieDescription"], (double)rdr["Price"], (bool)rdr["Is3D"], (int)rdr["MovieLength"], (int)rdr["Commercial"], (int)rdr["Trailer"], (string)rdr["MovieImage"]));
            } 
            
        }
        finally
        {
         
            if (rdr != null)
            {
                rdr.Close();
            }

            
            if (Connection != null)
            {
                Connection.Close();
            }
        }
        return movies;  
    }

    public int Insert(Movie movie)
    {
        int succeed = 0;

        string query = "INSERT INTO Movie(Title, MovieDescription, Price, Is3D, MovieLength, Commercial, Trailer, MovieImage) VALUES (@title, @description, @price, @is3d, @length, @commercials, @trailer, @image)";

        try
        {
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@title", SqlDbType.Char).Value = movie.Title;
            cmd.Parameters.Add("@description", SqlDbType.Char).Value = movie.Description;
            cmd.Parameters.Add("@price", SqlDbType.Float).Value = movie.Price;
            cmd.Parameters.Add("@is3d", SqlDbType.Bit).Value = movie.Is3d;
            cmd.Parameters.Add("@Length", SqlDbType.Int).Value = movie.Length;
            cmd.Parameters.Add("@commercials", SqlDbType.Int).Value = movie.Commercials;
            cmd.Parameters.Add("@trailer", SqlDbType.Int).Value = movie.Trailer;
            cmd.Parameters.Add("@image", SqlDbType.Char).Value = movie.Image;

            Connection.Open();
            succeed = cmd.ExecuteNonQuery();

        }
        catch (SqlException)
        {
            succeed = -1;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Connection.Close();
        }
        return succeed;
    }

    public int Update(Movie movie)
    {
        int succeed = 0;

        string query = "UPDATE Movie SET MovieDescription = @description, Price =  @price, Is3D = @is3d, MovieLength = @length, Commercial = @commercials, Trailer = @trailer, MovieImage = @image where Title = @title";
        try
        {
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@title", SqlDbType.Char).Value = movie.Title;
            cmd.Parameters.Add("@description", SqlDbType.Char).Value = movie.Description;
            cmd.Parameters.Add("@price", SqlDbType.Float).Value = movie.Price;
            cmd.Parameters.Add("@is3d", SqlDbType.Bit).Value = movie.Is3d;
            cmd.Parameters.Add("@Length", SqlDbType.Int).Value = movie.Length;
            cmd.Parameters.Add("@commercials", SqlDbType.Int).Value = movie.Commercials;
            cmd.Parameters.Add("@trailer", SqlDbType.Int).Value = movie.Trailer;
            cmd.Parameters.Add("@image", SqlDbType.Char).Value = movie.Image;

            Connection.Open();
            succeed = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            succeed = -1;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Connection.Close();
        }
        return succeed;
    }

    public int Delete(int id)
    {
        string query = "delete from movie where ID = @id";
        int succeed = 0;

        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            succeed = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            succeed = -1;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Connection.Close();
        }

        return succeed;
    }


    //public virtual SQLConnection con
    //{
    //    get;
    //    set;
    //}

    //public virtual Movie mov
    //{
    //    get;
    //    set;
    //}

    //public virtual Movie BuildMovie(DataRow dr)
    //{
    //    throw new System.NotImplementedException();
    //}

    //public virtual List<Movie> GetAllMovies()
    //{
    //    throw new System.NotImplementedException();
    //}

    //public virtual List<Movie> MiscWhere(string query, SQLParameter[] parameter = null)
    //{
    //    throw new System.NotImplementedException();
    //}

    //public virtual Movie SingleWhere(string query, SQLParameter[] parameter = null)
    //{
    //    throw new System.NotImplementedException();
    //}

    //public virtual Movie GetMovieByTitle(string title)
    //{
    //    throw new System.NotImplementedException();
    //}

}

